﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using HRM.DAL.DAO;
using HRM.Models;

namespace HRM.DBGatway
{
    public class EmployeeGateway:DbGateway
    {
        public List<Employee> SearchEmployeeById(int empId)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format(@"SELECT Employees.Name, Employees.EmployeeId, Employees.FathersName, Employees.MothersName, Employees.Address, Employees.Sex, 
                      Employees.Blood, Employees.Email, Employees.DOB, Employees.JoiningDate, Employees.ContractFinishDate, Employees.Education,Employees.Resume, Employees.Skill,
                      Employees.Nationality, Employees.NID, Employees.Status, Catagories.EmpCatagory,Designations.Title, Projects.ProjectName
                      FROM Employees INNER JOIN
                      Designations ON Employees.DesignationID = Designations.Id INNER JOIN
                      Catagories ON Employees.CatagoryID = Catagories.CatagoryID INNER JOIN
                      Projects ON Employees.ProjectID = Projects.ProjectID
                      WHERE (Employees.EmployeeId = '"+empId+"')");
                SqlDataReader reader = CommandObj.ExecuteReader();
                List<Employee> employees = new List<Employee>();
                
                while (reader.Read())
                {
                    Employee employee =new Employee();
                    Designation designation=new Designation();
                    Catagory catagory=new Catagory();
                    Project project=new Project();
                    employee.Name = reader[0].ToString();
                    employee.EmployeeId = Convert.ToInt32(reader[1]);
                    employee.FathersName = reader[2].ToString();
                    employee.MothersName = reader[3].ToString();
                    employee.Address = reader[4].ToString();
                    employee.Sex = reader[5].ToString();
                    employee.Blood = reader[6].ToString();
                    employee.Email = reader[7].ToString();
                    employee.DOB = Convert.ToDateTime(reader[8]);
                    employee.JoiningDate = Convert.ToDateTime(reader[9]);
                    employee.ContractFinishDate =Convert.ToDateTime(reader[10]);
                    employee.Education = reader[11].ToString();
                    employee.Resume = reader[12].ToString();
                    employee.Skill = reader[13].ToString();
                    employee.Nationality = reader[14].ToString();
                    employee.NID = reader[15].ToString();
                    employee.Status = reader[16].ToString();
                    catagory.EmpCatagory = reader[17].ToString();
                    designation.Title = reader[18].ToString();
                    project.ProjectName = reader[19].ToString();
                    employee.Catagory = catagory;
                    employee.Designation = designation;
                    employee.Project = project;
                    employees.Add(employee);
                }
                return employees;
            }
            catch (Exception ex)
            {
                throw new Exception("Unable to read Employee Information",ex);
            }
            finally
            {
                ConnectionObj.Close();
            }
        }
    }
}